﻿using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Texpro
{

    class PCDB
    {
        
        static OleDbConnection sql_conn = new OleDbConnection();

        private static bool connect_db()
        {
            try
            {
                String db_connection_string = PCPUB.m_oledb_connection.ToString();
                disconnect_db();
                sql_conn = new OleDbConnection(db_connection_string);
                sql_conn.Open();
                return true;
            }
            catch (Exception ex)
            {
                PCMSG.ShowError(ex);
                return false;
            }
        }

        private static void disconnect_db()
        {
            if (sql_conn.State == ConnectionState.Open)
            {
                sql_conn.Close();
                sql_conn.Dispose();
            }
        }

        public static bool connect_db(SqlConnection p_sql_connection)
        {
            try
            {
                String db_connection_string = PCPUB.m_sqldb_connection.ToString();
                disconnect_db(p_sql_connection);
                p_sql_connection = new SqlConnection(db_connection_string);
                p_sql_connection.Open();
                return true;
            }
            catch (Exception ex)
            {
                PCMSG.ShowError(ex);
                return false;
            }
        }

        public static void disconnect_db(SqlConnection p_sql_connection)
        {
            if (p_sql_connection.State == ConnectionState.Open)
            {
                p_sql_connection.Close();
                p_sql_connection.Dispose();
            }
        }
                
        // Login

        public static bool tis_user_validate(string p_user_id, string p_password)
        {
            String sql_text = String.Empty;
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            int user_count = 0;
            PCPUB.m_user_id = String.Empty;
            PCPUB.m_user_name = String.Empty;
            PCPUB.m_user_role_id = String.Empty;
            //
            sql_text = String.Empty;
            sql_text += " SELECT ISNULL(COUNT(1),0) [user_count],";
            sql_text += " ISNULL(MIN(user_id),'') [user_id],";
            sql_text += " ISNULL(MIN(user_name),'') user_name,";
            sql_text += " ISNULL(MIN(user_role_id),'') user_role_id";
            sql_text += " FROM tis_user ";
            sql_text += " WHERE user_id = '" + p_user_id + "'";
            sql_text += " AND user_password = '" + p_password + "'";
            //
            connect_db();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = sql_text.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                user_count = (int)sql_read["user_count"];
                PCPUB.m_user_id = sql_read["user_id"].ToString();
                PCPUB.m_user_name = sql_read["user_name"].ToString();
                PCPUB.m_user_role_id = sql_read["user_role_id"].ToString();
                if (PCPUB.m_user_role_id.Equals("ADMIN"))
                {
                    PCPUB.m_user_role_name = "Administrator";
                }
                else
                {
                    PCPUB.m_user_role_name = "General User";
                }
            }
            sql_read.Close();
            disconnect_db();
            if (user_count == 1)
            {
                return true;
            }
            else
            {
                PCPUB.m_user_id = String.Empty;
                PCPUB.m_user_name = String.Empty;
                PCPUB.m_user_role_id = String.Empty;
                return false;
            }
        }

        // yarn count

        public static bool insert_tis_yarn_count(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String yarn_count_id = String.Empty;
                String yarn_count_name = String.Empty;
                //int i = 0;
                //
                yarn_count_id = "'" + p_data_values[0].ToString() + "'";
                yarn_count_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery = String.Empty;
                strQuery += "INSERT INTO [tis_yarn_count] (";
                strQuery += "[yarn_count_id],";
                strQuery += "[yarn_count_name]";
                strQuery += ") VALUES (";
                strQuery += yarn_count_id + ",";
                strQuery += yarn_count_name + ")";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.Show(e.Message);
                return false;
            }
        }

        public static bool update_tis_yarn_count(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String yarn_count_id = String.Empty;
                String yarn_count_name = String.Empty;
                //int i = 0;
                //
                yarn_count_id = "'" + p_data_values[0].ToString() + "'";
                yarn_count_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery += " UPDATE [tis_yarn_count] SET ";
                strQuery += " [yarn_count_name] = " + yarn_count_name;
                strQuery += " WHERE [yarn_count_id] = " + yarn_count_id;
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.ShowError(e);
                return false;
            }
        }

        public static bool delete_tis_yarn_count(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_yarn_count]";
                strQuery += " WHERE [yarn_count_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        // color

        public static bool insert_tis_color(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String color_id = String.Empty;
                String color_name = String.Empty;
                //int i = 0;
                //
                color_id = "'" + p_data_values[0].ToString() + "'";
                color_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery = String.Empty;
                strQuery += "INSERT INTO [tis_color] (";
                strQuery += "[color_id],";
                strQuery += "[color_name]";
                strQuery += ") VALUES (";
                strQuery += color_id + ",";
                strQuery += color_name + ")";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.Show(e.Message);
                return false;
            }
        }

        public static bool update_tis_color(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String color_id = String.Empty;
                String color_name = String.Empty;
                //int i = 0;
                //
                color_id = "'" + p_data_values[0].ToString() + "'";
                color_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery += " UPDATE [tis_color] SET ";
                strQuery += " [color_name] = " + color_name;
                strQuery += " WHERE [color_id] = " + color_id;
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.ShowError(e);
                return false;
            }
        }

        public static bool delete_tis_color(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_color]";
                strQuery += " WHERE [color_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.ShowError(e);
                return false;
            }
        }

        public static bool delete_tis_yarn_purchase_iw(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_yarn_purchase_iw_detail]";
                strQuery += " WHERE [yarn_purchase_iw_id] = '" + p_id + "'";
                strQuery += " AND [yarn_purchase_iw_date] = '" + p_date + "';";
                strQuery += "DELETE FROM [tis_yarn_purchase_iw]";
                strQuery += " WHERE [yarn_purchase_iw_id] = '" + p_id + "'";
                strQuery += " AND [yarn_purchase_iw_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_yarn_sales_ow(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_yarn_sales_ow_detail]";
                strQuery += " WHERE [yarn_sales_ow_id] = '" + p_id + "'";
                strQuery += " AND [yarn_sales_ow_date] = '" + p_date + "';";
                strQuery += "DELETE FROM [tis_yarn_sales_ow]";
                strQuery += " WHERE [yarn_sales_ow_id] = '" + p_id + "'";
                strQuery += " AND [yarn_sales_ow_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_yarn_material_iw(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_yarn_material_iw_detail]";
                strQuery += " WHERE [yarn_material_iw_id] = '" + p_id + "'";
                strQuery += " AND [yarn_material_iw_date] = '" + p_date + "';";
                strQuery += "DELETE FROM [tis_yarn_material_iw]";
                strQuery += " WHERE [yarn_material_iw_id] = '" + p_id + "'";
                strQuery += " AND [yarn_material_iw_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_dyeing_ow(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_dyeing_ow_detail]";
                strQuery += " WHERE [dyeing_ow_id] = '" + p_id + "'";
                strQuery += " AND [dyeing_ow_date] = '" + p_date + "';";
                strQuery += " DELETE FROM [tis_dyeing_ow]";
                strQuery += " WHERE [dyeing_ow_id] = '" + p_id + "'";
                strQuery += " AND [dyeing_ow_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_dyeing_iw(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                //if (!PCDB.m_get_payment_status("FMTR202", p_date, p_id).Equals("NOTPAID"))
                //{
                //    PCMSG.ShowError("Cannot delete this record. Payment already made.");
                //    return false;
                //}
                //else
                //{
                    strQuery = String.Empty;
                    strQuery += " DELETE FROM [tis_dyeing_iw_detail]";
                    strQuery += " WHERE [dyeing_iw_id] = '" + p_id + "'";
                    strQuery += " AND [dyeing_iw_date] = '" + p_date + "';";
                    strQuery += " DELETE FROM [tis_dyeing_iw]";
                    strQuery += " WHERE [dyeing_iw_id] = '" + p_id + "'";
                    strQuery += " AND [dyeing_iw_date] = '" + p_date + "';";
                    //
                    connect_db();
                    sqlComm.Connection = sql_conn;
                    sqlComm.CommandText = strQuery.ToString();
                    sqlComm.ExecuteNonQuery();
                    sqlComm.Dispose();
                    disconnect_db();
                    //
                    return true;
                //}
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_weaving_ow(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_weaving_ow_detail]";
                strQuery += " WHERE [weaving_ow_id] = '" + p_id + "'";
                strQuery += " AND [weaving_ow_date] = '" + p_date + "';";
                strQuery += " DELETE FROM [tis_weaving_ow]";
                strQuery += " WHERE [weaving_ow_id] = '" + p_id + "'";
                strQuery += " AND [weaving_ow_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_weaving_iw(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                if (!PCDB.m_get_payment_status("FMTR302", p_date, p_id).Equals("NOTPAID"))
                {
                    PCMSG.ShowError("Cannot delete this record. Payment already made.");
                    return false;
                }
                else
                {
                    strQuery = String.Empty;
                    strQuery += " DELETE FROM [tis_weaving_iw_detail]";
                    strQuery += " WHERE [weaving_iw_id] = '" + p_id + "'";
                    strQuery += " AND [weaving_iw_date] = '" + p_date + "';";
                    strQuery += " DELETE FROM [tis_weaving_iw]";
                    strQuery += " WHERE [weaving_iw_id] = '" + p_id + "'";
                    strQuery += " AND [weaving_iw_date] = '" + p_date + "';";
                    //
                    connect_db();
                    sqlComm.Connection = sql_conn;
                    sqlComm.CommandText = strQuery.ToString();
                    sqlComm.ExecuteNonQuery();
                    sqlComm.Dispose();
                    disconnect_db();
                    //
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_weaving_adj(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_weaving_adj_detail]";
                strQuery += " WHERE [weaving_adj_id] = '" + p_id + "'";
                strQuery += " AND [weaving_adj_date] = '" + p_date + "';";
                strQuery += " DELETE FROM [tis_weaving_adj]";
                strQuery += " WHERE [weaving_adj_id] = '" + p_id + "'";
                strQuery += " AND [weaving_adj_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_washing_ow(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_washing_ow_detail]";
                strQuery += " WHERE [washing_ow_id] = '" + p_id + "'";
                strQuery += " AND [washing_ow_date] = '" + p_date + "';";
                strQuery += " DELETE FROM [tis_washing_ow]";
                strQuery += " WHERE [washing_ow_id] = '" + p_id + "'";
                strQuery += " AND [washing_ow_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_washing_iw(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                if (!PCDB.m_get_payment_status("FMTR402", p_date, p_id).Equals("NOTPAID"))
                {
                    PCMSG.ShowError("Cannot delete this record. Payment already made.");
                    return false;
                }
                else
                {
                    strQuery = String.Empty;
                    strQuery += " DELETE FROM [tis_washing_iw_detail]";
                    strQuery += " WHERE [washing_iw_id] = '" + p_id + "'";
                    strQuery += " AND [washing_iw_date] = '" + p_date + "';";
                    strQuery += " DELETE FROM [tis_washing_iw]";
                    strQuery += " WHERE [washing_iw_id] = '" + p_id + "'";
                    strQuery += " AND [washing_iw_date] = '" + p_date + "';";
                    //
                    connect_db();
                    sqlComm.Connection = sql_conn;
                    sqlComm.CommandText = strQuery.ToString();
                    sqlComm.ExecuteNonQuery();
                    sqlComm.Dispose();
                    disconnect_db();
                    //
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_tailoring_ow(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_tailoring_ow_detail]";
                strQuery += " WHERE [tailoring_ow_id] = '" + p_id + "'";
                strQuery += " AND [tailoring_ow_date] = '" + p_date + "';";
                strQuery += " DELETE FROM [tis_tailoring_ow]";
                strQuery += " WHERE [tailoring_ow_id] = '" + p_id + "'";
                strQuery += " AND [tailoring_ow_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_tailoring_iw(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                if (!PCDB.m_get_payment_status("FMTR502", p_date, p_id).Equals("NOTPAID"))
                {
                    PCMSG.ShowError("Cannot delete this record. Payment already made.");
                    return false;
                }
                else
                {
                    strQuery = String.Empty;
                    strQuery += " DELETE FROM [tis_tailoring_iw_detail]";
                    strQuery += " WHERE [tailoring_iw_id] = '" + p_id + "'";
                    strQuery += " AND [tailoring_iw_date] = '" + p_date + "';";
                    strQuery += " DELETE FROM [tis_tailoring_iw]";
                    strQuery += " WHERE [tailoring_iw_id] = '" + p_id + "'";
                    strQuery += " AND [tailoring_iw_date] = '" + p_date + "';";
                    //
                    connect_db();
                    sqlComm.Connection = sql_conn;
                    sqlComm.CommandText = strQuery.ToString();
                    sqlComm.ExecuteNonQuery();
                    sqlComm.Dispose();
                    disconnect_db();
                    //
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        // payment

        public static bool delete_tis_payment(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_payment_detail]";
                strQuery += " WHERE [payment_id] = '" + p_id + "'";
                strQuery += " AND [payment_date] = '" + p_date + "';";
                strQuery += " DELETE FROM [tis_payment]";
                strQuery += " WHERE [payment_id] = '" + p_id + "'";
                strQuery += " AND [payment_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        // receipt

        public static bool delete_tis_receipt(String p_id, String p_date)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_receipt_detail]";
                strQuery += " WHERE [payment_id] = '" + p_id + "'";
                strQuery += " AND [payment_date] = '" + p_date + "';";
                strQuery += " DELETE FROM [tis_receipt]";
                strQuery += " WHERE [receipt_id] = '" + p_id + "'";
                strQuery += " AND [receipt_date] = '" + p_date + "';";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        // ADMIN

        // yarn name

        public static bool insert_tis_yarn_name(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String yarn_name_id = String.Empty;
                String yarn_name_name = String.Empty;
                //int i = 0;
                //
                yarn_name_id = "'" + p_data_values[0].ToString() + "'";
                yarn_name_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery = String.Empty;
                strQuery += "INSERT INTO [tis_yarn_name] (";
                strQuery += "[yarn_name_id],";
                strQuery += "[yarn_name_name]";
                strQuery += ") VALUES (";
                strQuery += yarn_name_id + ",";
                strQuery += yarn_name_name + ")";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.Show(e.Message);
                return false;
            }
        }

        public static bool update_tis_yarn_name(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String yarn_name_id = String.Empty;
                String yarn_name_name = String.Empty;
                //int i = 0;
                //
                yarn_name_id = "'" + p_data_values[0].ToString() + "'";
                yarn_name_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery += " UPDATE [tis_yarn_name] SET ";
                strQuery += " [yarn_name_name] = " + yarn_name_name;
                strQuery += " WHERE [yarn_name_id] = " + yarn_name_id;
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.ShowError(e);
                return false;
            }
        }

        public static bool delete_tis_yarn_name(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_yarn_name]";
                strQuery += " WHERE [yarn_name_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        // city

        public static bool insert_tis_city(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String city_id = String.Empty;
                String city_name = String.Empty;
                //
                city_id = "'" + p_data_values[0].ToString() + "'";
                city_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery = String.Empty;
                strQuery += "INSERT INTO [tis_city] (";
                strQuery += "[city_id],";
                strQuery += "[city_name]";
                strQuery += ") VALUES (";
                strQuery += city_id + ",";
                strQuery += city_name + ")";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.Show(e.Message);
                return false;
            }
        }

        public static bool update_tis_city(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String city_id = String.Empty;
                String city_name = String.Empty;
                //int i = 0;
                //
                city_id = "'" + p_data_values[0].ToString() + "'";
                city_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery += " UPDATE [tis_city] SET ";
                strQuery += " [city_name] = " + city_name;
                strQuery += " WHERE [city_id] = " + city_id;
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.ShowError(e);
                return false;
            }
        }

        public static bool delete_tis_city(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_city]";
                strQuery += " WHERE [city_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }
        
        // vendor

        public static bool delete_tis_vendor(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_vendor]";
                strQuery += " WHERE [vendor_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_user(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_user]";
                strQuery += " WHERE [user_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_tailor_iw(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_tailor_iw]";
                strQuery += " WHERE [tailor_iw_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_washing_ow(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_washing_ow]";
                strQuery += " WHERE [washing_ow_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static bool delete_tis_washing_iw(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_washing_iw]";
                strQuery += " WHERE [washing_iw_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        public static double m_get_unit_conversion(String p_from_unit_id, String p_to_unit_id)
        {
            String strQuery = String.Empty;
            OleDbConnection sqlConn = new OleDbConnection();
            OleDbCommand sqlComm = new OleDbCommand();
            OleDbDataReader sqlRead;// = new OleDbDataReader();
            double dblReturn = 0;
            //           
            sqlConn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sqlConn.Open();
            //
            strQuery = String.Empty;
            strQuery += "select isnull(conversion,0) return_value from tis_unit_conversion where from_unit_id = '" + p_from_unit_id.ToString() + "' and to_unit_id = '" + p_to_unit_id.ToString() + "'";
            //
            sqlComm.Connection = sqlConn;
            sqlComm.CommandText = strQuery.ToString();
            sqlRead = sqlComm.ExecuteReader();
            //
            if (sqlRead.Read())
            {
                dblReturn = double.Parse(sqlRead["return_value"].ToString());
            }
            //
            sqlRead.Close();
            sqlConn.Close();
            sqlRead.Dispose();
            sqlComm.Dispose();
            sqlConn.Dispose();
            //
            return dblReturn;
        }

        // Design

        public static bool insert_tis_design(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String design_id = String.Empty;
                String design_name = String.Empty;
                //int i = 0;
                //
                design_id = "'" + p_data_values[0].ToString() + "'";
                design_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery = String.Empty;
                strQuery += "INSERT INTO [tis_design] (";
                strQuery += "[design_id],";
                strQuery += "[design_name]";
                strQuery += ") VALUES (";
                strQuery += design_id + ",";
                strQuery += design_name + ")";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.Show(e.Message);
                return false;
            }
        }

        public static bool update_tis_design(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String design_id = String.Empty;
                String design_name = String.Empty;
                //int i = 0;
                //
                design_id = "'" + p_data_values[0].ToString() + "'";
                design_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery += " UPDATE [tis_design] SET ";
                strQuery += " [design_name] = " + design_name;
                strQuery += " WHERE [design_id] = " + design_id;
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.ShowError(e);
                return false;
            }
        }

        public static bool delete_tis_design(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += "DELETE FROM [tis_design]";
                strQuery += " WHERE [design_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        // Size

        public static bool insert_tis_size(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String size_id = String.Empty;
                String size_name = String.Empty;
                //int i = 0;
                //
                size_id = "'" + p_data_values[0].ToString() + "'";
                size_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery = String.Empty;
                strQuery += " INSERT INTO [tis_size] (";
                strQuery += " [size_id],";
                strQuery += " [size_name]";
                strQuery += ") VALUES (";
                strQuery += size_id + ",";
                strQuery += size_name + ")";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.Show(e.Message);
                return false;
            }
        }

        public static bool update_tis_size(ArrayList p_data_values)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                String size_id = String.Empty;
                String size_name = String.Empty;
                //int i = 0;
                //
                size_id = "'" + p_data_values[0].ToString() + "'";
                size_name = "'" + p_data_values[1].ToString() + "'";
                //
                strQuery += " UPDATE [tis_size] SET ";
                strQuery += " [size_name] = " + size_name;
                strQuery += " WHERE [size_id] = " + size_id;
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch (Exception e)
            {
                PCMSG.ShowError(e);
                return false;
            }
        }

        public static bool delete_tis_size(String p_id)
        {
            try
            {
                String strQuery = String.Empty;
                OleDbCommand sqlComm = new OleDbCommand();
                //
                strQuery = String.Empty;
                strQuery += " DELETE FROM [tis_size]";
                strQuery += " WHERE [size_id] = '" + p_id + "'";
                //
                connect_db();
                sqlComm.Connection = sql_conn;
                sqlComm.CommandText = strQuery.ToString();
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
                disconnect_db();
                //
                return true;
            }
            catch
            {
                return false;
            }
        }

        // unit

        public static double m_get_unit_factor(String p_unit_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL([unit_factor],0) [unit_factor]";
            str_query += " FROM [tis_unit] ";
            str_query += " WHERE [unit_id] = '" + p_unit_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["unit_factor"].ToString());
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        // get wages

        public static double m_get_tailoring_wage_gross(String p_design_id, String p_size_id, String p_unit_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0.00;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL([wage_gross],0.00) wage_gross";
            str_query += " FROM [tis_tailoring_wage] ";
            str_query += " WHERE [design_id] = '" + p_design_id + "'";
            str_query += " AND [size_id] = '" + p_size_id + "'";
            str_query += " AND [unit_id] = '" + p_unit_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["wage_gross"].ToString());
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_tailoring_wage_nett(String p_design_id, String p_size_id, String p_unit_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0.00;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL([wage_nett],0.00) wage_nett";
            str_query += " FROM [tis_tailoring_wage] ";
            str_query += " WHERE [design_id] = '" + p_design_id + "'";
            str_query += " AND [size_id] = '" + p_size_id + "'";
            str_query += " AND [unit_id] = '" + p_unit_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["wage_nett"].ToString());
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_weaving_wage_gross(String p_design_id, String p_size_id, String p_unit_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0.00;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL([wage_gross],0.00) wage_gross";
            str_query += " FROM [tis_weaving_wage] ";
            str_query += " WHERE [design_id] = '" + p_design_id + "'";
            str_query += " AND [size_id] = '" + p_size_id + "'";
            str_query += " AND [unit_id] = '" + p_unit_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["wage_gross"].ToString());
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_weaving_wage_nett(String p_design_id, String p_size_id, String p_unit_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0.00;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL([wage_nett],0.00) wage_nett";
            str_query += " FROM [tis_weaving_wage] ";
            str_query += " WHERE [design_id] = '" + p_design_id + "'";
            str_query += " AND [size_id] = '" + p_size_id + "'";
            str_query += " AND [unit_id] = '" + p_unit_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["wage_nett"].ToString());
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_washing_wage_gross(String p_design_id, String p_size_id, String p_unit_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0.00;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL([wage_gross],0.00) wage_gross";
            str_query += " FROM [tis_washing_wage] ";
            str_query += " WHERE [design_id] = '" + p_design_id + "'";
            str_query += " AND [size_id] = '" + p_size_id + "'";
            str_query += " AND [unit_id] = '" + p_unit_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["wage_gross"].ToString());
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_washing_wage_nett(String p_design_id, String p_size_id, String p_unit_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0.00;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL([wage_nett],0.00) wage_nett";
            str_query += " FROM [tis_washing_wage] ";
            str_query += " WHERE [design_id] = '" + p_design_id + "'";
            str_query += " AND [size_id] = '" + p_size_id + "'";
            str_query += " AND [unit_id] = '" + p_unit_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["wage_nett"].ToString());
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static String m_get_payment_status(String p_tran_type_id, String p_tran_date, String p_tran_id)
        {
            String str_query = String.Empty;
            String str_return = "PAID";
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL(payment_id,'NOTPAID') payment_status";
            str_query += " FROM vw_payment_status a";
            str_query += " WHERE a.tran_type_id = '" + p_tran_type_id + "'";
            str_query += " AND CONVERT(VARCHAR(10),a.tran_date,121) = '" + p_tran_date + "'";
            str_query += " AND a.tran_id = '" + p_tran_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                str_return = sql_read["payment_status"].ToString();
            }
            else
            {
                str_return = "NOTPAID";
            }
            sql_read.Close();
            sql_conn.Close();
            return str_return;
        }

        public static double m_get_weaver_prev_week_weight_os(String p_weaver_id, String p_tran_date)
        {
            String str_query = String.Empty;
            double dbl_return = 0;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL(SUM(ISNULL([sum_weight_ow],0)-ISNULL([sum_weight_iw],0)),0) weaver_prev_week_weight_os";
            str_query += " FROM [vw_weaving_summary_os] a";
            str_query += " WHERE a.[weaver_id]  = '" + p_weaver_id + "'";
            str_query += " AND CONVERT(VARCHAR(10),a.[weaving_ow_date],121) < DATEADD (d , -6 , '" + p_tran_date + "')";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["weaver_prev_week_weight_os"].ToString());
            }
            else
            {
                dbl_return = 0;
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_weaver_previous_week_weight_os(String p_date, String p_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0;
            String str_date_start = String.Empty;
            String str_date_end = String.Empty;
            String str_vendor_id = String.Empty;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            //
            str_query = String.Empty;
            str_query += " SELECT CONVERT(VARCHAR(10),MIN(a.[tran_date]),121) date_start, CONVERT(VARCHAR(10),MAX(a.[tran_date]),121) date_end, MIN(b.[vendor_id]) vendor_id";
            str_query += " FROM [vw_payment_detail] a RIGHT JOIN [vw_payment] b ON b.[payment_date]  = a.[payment_date] AND b.[payment_id] = a.[payment_id]";
            str_query += " WHERE a.[payment_date]  = '" + p_date + "'";
            str_query += " AND a.[payment_id] = '" + p_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                str_date_start = sql_read["date_start"].ToString();
                str_date_end = sql_read["date_end"].ToString();
                str_vendor_id = sql_read["vendor_id"].ToString();
            }
            else
            {
                str_date_start = p_date.ToString();
                str_date_end = p_date.ToString();
                str_vendor_id = String.Empty;
            }
            sql_read.Close();
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL(SUM(ISNULL([sum_weight_ow],0)-ISNULL([sum_weight_iw],0)-ISNULL([sum_weight_adj],0)),0) weaver_prev_week_weight_os";
            str_query += " FROM [vw_weaving_summary_os] a";
            str_query += " WHERE a.[weaver_id]  = '" + str_vendor_id + "'";
            str_query += " AND CONVERT(VARCHAR(10),a.[weaving_ow_date],121) < DATEADD(d,-6,'" + p_date + "')";
            // sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            // sql_conn.Open();
            // sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["weaver_prev_week_weight_os"].ToString());
            }
            else
            {
                dbl_return = 0;
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_weaver_current_week_weight_os(String p_date, String p_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0;
            String str_date_start = String.Empty;
            String str_date_end = String.Empty;
            String str_vendor_id = String.Empty;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            //
            str_query = String.Empty;
            str_query += " SELECT CONVERT(VARCHAR(10),MIN(a.[tran_date]),121) date_start, CONVERT(VARCHAR(10),MAX(a.[tran_date]),121) date_end, MIN(b.[vendor_id]) vendor_id";
            str_query += " FROM [vw_payment_detail] a RIGHT JOIN [vw_payment] b ON b.[payment_date]  = a.[payment_date] AND b.[payment_id] = a.[payment_id]";
            str_query += " WHERE a.[payment_date]  = '" + p_date + "'";
            str_query += " AND a.[payment_id] = '" + p_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                str_date_start = sql_read["date_start"].ToString();
                str_date_end = sql_read["date_end"].ToString();
                str_vendor_id = sql_read["vendor_id"].ToString();
            }
            else
            {
                str_date_start = p_date.ToString();
                str_date_end = p_date.ToString();
                str_vendor_id = String.Empty;
            }
            sql_read.Close();
            //
            str_query = String.Empty;
            str_query += " SELECT SUM(ISNULL([sum_weight_ow],0)) weaver_curr_week_weight_os";
            str_query += " FROM [vw_weaving_summary_os] a";
            str_query += " WHERE a.[weaver_id]  = '" + str_vendor_id + "'";
            str_query += " AND CONVERT(VARCHAR(10),a.[weaving_ow_date],121) BETWEEN DATEADD(d,-6,'" + p_date + "') AND '"+ p_date + "'";
            //sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            //sql_conn.Open();
            //sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["weaver_curr_week_weight_os"].ToString());
            }
            else
            {
                dbl_return = 0;
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

        public static double m_get_weaver_current_week_weight_adj(String p_date, String p_id)
        {
            String str_query = String.Empty;
            double dbl_return = 0;
            String str_date_start = String.Empty;
            String str_date_end = String.Empty;
            String str_vendor_id = String.Empty;
            OleDbConnection sql_conn = new OleDbConnection();
            OleDbCommand sql_comm = new OleDbCommand();
            OleDbDataReader sql_read;
            //
            //
            str_query = String.Empty;
            str_query += " SELECT CONVERT(VARCHAR(10),MIN(a.[tran_date]),121) date_start, CONVERT(VARCHAR(10),MAX(a.[tran_date]),121) date_end, MIN(b.[vendor_id]) vendor_id";
            str_query += " FROM [vw_payment_detail] a RIGHT JOIN [vw_payment] b ON b.[payment_date]  = a.[payment_date] AND b.[payment_id] = a.[payment_id]";
            str_query += " WHERE a.[payment_date]  = '" + p_date + "'";
            str_query += " AND a.[payment_id] = '" + p_id + "'";
            sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            sql_conn.Open();
            sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                str_date_start = sql_read["date_start"].ToString();
                str_date_end = sql_read["date_end"].ToString();
                str_vendor_id = sql_read["vendor_id"].ToString();
            }
            else
            {
                str_date_start = p_date.ToString();
                str_date_end = p_date.ToString();
                str_vendor_id = String.Empty;
            }
            sql_read.Close();
            //
            str_query = String.Empty;
            str_query += " SELECT ISNULL(SUM(ISNULL([sum_weight],0)),0) weaver_curr_week_weight_adj";
            str_query += " FROM [vw_weaving_summary_adj] a";
            str_query += " WHERE a.[weaver_id]  = '" + str_vendor_id + "'";
            str_query += " AND CONVERT(VARCHAR(10),a.[weaving_adj_date],121) BETWEEN DATEADD(d,-6,'" + p_date + "') AND '" + p_date + "'";
            //sql_conn.ConnectionString = PCPUB.m_oledb_connection.ToString();
            //sql_conn.Open();
            //sql_comm.Connection = sql_conn;
            sql_comm.CommandText = str_query.ToString();
            sql_read = sql_comm.ExecuteReader();
            if (sql_read.Read())
            {
                dbl_return = double.Parse(sql_read["weaver_curr_week_weight_adj"].ToString());
            }
            else
            {
                dbl_return = 0;
            }
            sql_read.Close();
            sql_conn.Close();
            return dbl_return;
        }

    }

}

